-- Kunena 2.0

ALTER TABLE	`#__kunena_categories`	MODIFY `ordering` smallint(6) NOT NULL default '0' AFTER `admin_recurse`; -- Also new in Kunena 1.6.4
ALTER TABLE	`#__kunena_categories`	DROP `alert_admin`;
ALTER TABLE	`#__kunena_categories`	DROP `moderators`;
ALTER TABLE	`#__kunena_categories`	DROP `future2`;
ALTER TABLE	`#__kunena_categories`	DROP `id_last_msg`;
ALTER TABLE	`#__kunena_categories`	DROP `time_last_msg`;
ALTER TABLE	`#__kunena_categories`	DROP INDEX `parent`;
ALTER TABLE	`#__kunena_categories`	DROP INDEX `msg_id`;
ALTER TABLE	`#__kunena_categories`	CHANGE `parent` `parent_id` INT( 11 ) NULL DEFAULT '0';
ALTER TABLE	`#__kunena_categories`	CHANGE `cat_emoticon` `icon_id` TINYINT( 4 ) NOT NULL DEFAULT '0';
ALTER TABLE	`#__kunena_categories`	ADD `last_topic_id` int(11) NOT NULL default '0' AFTER `numPosts`;
ALTER TABLE	`#__kunena_categories`	ADD `last_post_id` int(11) NOT NULL default '0' AFTER `last_topic_id`;
ALTER TABLE	`#__kunena_categories`	ADD `last_post_time` int(11) NOT NULL default '0' AFTER `last_post_id`;
ALTER TABLE	`#__kunena_categories`	ADD `params` text NOT NULL AFTER `last_post_time`;
ALTER TABLE	`#__kunena_categories`	ADD `channels` TEXT NULL DEFAULT NULL AFTER `published`;
ALTER TABLE	`#__kunena_categories`	ADD `topic_ordering` VARCHAR(16) NOT NULL default 'lastpost' AFTER `allow_polls`;
ALTER TABLE	`#__kunena_categories`	ADD KEY `parent_id` (parent_id);

ALTER TABLE	`#__kunena_topics`		ADD INDEX `posts` ( `posts` );

ALTER TABLE	`#__kunena_messages`	MODIFY `ip` varchar(128) NULL AFTER `time`;

ALTER TABLE	`#__kunena_thankyou`	MODIFY `time` datetime NOT NULL;

ALTER TABLE	`#__kunena_users`		ADD INDEX `moderator` ( `moderator` ); -- Also new in Kunena 1.6.4
ALTER TABLE	`#__kunena_users`		CHANGE `ICQ` `icq` VARCHAR(50) NULL DEFAULT NULL;
ALTER TABLE	`#__kunena_users`		CHANGE `AIM` `aim` VARCHAR(50) NULL DEFAULT NULL;
ALTER TABLE	`#__kunena_users`		CHANGE `YIM` `yim` VARCHAR(50) NULL DEFAULT NULL;
ALTER TABLE	`#__kunena_users`		CHANGE `MSN` `msn` VARCHAR(50) NULL DEFAULT NULL;
ALTER TABLE	`#__kunena_users`		CHANGE `SKYPE` `skype` VARCHAR(50) NULL DEFAULT NULL;
ALTER TABLE	`#__kunena_users`		CHANGE `TWITTER` `twitter` VARCHAR(50) NULL DEFAULT NULL;
ALTER TABLE	`#__kunena_users`		CHANGE `FACEBOOK` `facebook` VARCHAR(50) NULL DEFAULT NULL;
ALTER TABLE	`#__kunena_users`		CHANGE `GTALK` `gtalk` VARCHAR(50) NULL DEFAULT NULL;
ALTER TABLE	`#__kunena_users`		CHANGE `MYSPACE` `myspace` VARCHAR(50) NULL DEFAULT NULL;
ALTER TABLE	`#__kunena_users`		CHANGE `LINKEDIN` `linkedin` VARCHAR(50) NULL DEFAULT NULL;
ALTER TABLE	`#__kunena_users`		CHANGE `DELICIOUS` `delicious` VARCHAR(50) NULL DEFAULT NULL;
ALTER TABLE	`#__kunena_users`		CHANGE `FRIENDFEED` `friendfeed` VARCHAR(50) NULL DEFAULT NULL;
ALTER TABLE	`#__kunena_users`		CHANGE `DIGG` `digg` VARCHAR(50) NULL DEFAULT NULL;
ALTER TABLE	`#__kunena_users`		CHANGE `BLOGSPOT` `blogspot` VARCHAR(50) NULL DEFAULT NULL;
ALTER TABLE	`#__kunena_users`		CHANGE `FLICKR` `flickr` VARCHAR(50) NULL DEFAULT NULL;
ALTER TABLE	`#__kunena_users`		CHANGE `BEBO` `bebo` VARCHAR(50) NULL DEFAULT NULL;

ALTER TABLE	`#__kunena_user_read`	MODIFY `time` datetime NOT NULL;


INSERT IGNORE INTO #__kunena_user_topics (user_id, topic_id, category_id, posts, last_post_id, owner)
	SELECT userid AS user_id, thread AS topic_id, catid AS category_id, COUNT(*) AS posts, MAX(id) AS last_post_id, MAX(IF(parent=0,1,0)) AS owner
	FROM #__kunena_messages WHERE userid>0 AND moved=0 AND hold=0
	GROUP BY user_id, topic_id;

INSERT INTO #__kunena_user_topics (user_id, topic_id, category_id, favorite)
	SELECT f.userid AS user_id, m.thread AS topic_id, m.catid AS category_id, 1 AS favorite
	FROM #__kunena_favorites AS f
	INNER JOIN #__kunena_messages AS m ON m.id=f.thread
	WHERE f.userid>0
	ON DUPLICATE KEY UPDATE favorite=1;

INSERT INTO #__kunena_user_topics (user_id, topic_id, category_id, subscribed)
	SELECT s.userid AS user_id, m.thread AS topic_id, m.catid AS category_id, 1 AS subscribed
	FROM #__kunena_subscriptions AS s
	INNER JOIN #__kunena_messages AS m ON m.id=s.thread
	WHERE s.userid>0
	ON DUPLICATE KEY UPDATE subscribed=1;

INSERT INTO #__kunena_user_categories (user_id, category_id, subscribed)
	SELECT userid AS user_id, catid AS category_id, 1 AS subscribed
	FROM #__kunena_subscriptions_categories
	WHERE userid>0
	ON DUPLICATE KEY UPDATE subscribed=VALUES(subscribed);

UPDATE #__kunena_user_categories AS c INNER JOIN #__kunena_sessions AS s ON c.user_id=s.userid
	SET c.allreadtime=FROM_UNIXTIME(s.lasttime);

UPDATE #__kunena_messages SET hold=3 WHERE parent=0 AND hold=2;

INSERT IGNORE INTO `#__kunena_topics`
	(id, category_id, subject, icon_id, locked, hold, ordering, hits, poll_id, moved_id)
	SELECT a.id, a.catid, a.subject, a.topic_emoticon, a.locked, a.hold, a.ordering, a.hits, p.id AS poll_id, 0 as moved_id
	FROM #__kunena_messages AS a
	LEFT JOIN #__kunena_polls AS p ON p.threadid=a.id
	WHERE a.parent = 0 AND a.moved = 0 AND a.id=a.thread
	GROUP BY a.id;


DROP TABLE IF EXISTS `#__kunena_attachments_bak`;
DROP TABLE IF EXISTS `#__kunena_favorites`;
DROP TABLE IF EXISTS `#__kunena_groups`;
DROP TABLE IF EXISTS `#__kunena_subscriptions`;
DROP TABLE IF EXISTS `#__kunena_subscriptions_categories`;
